package com.xj.server;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xj.util.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.*;


@Slf4j
@Service
//@EnableScheduling
public class ReadfileService {
    private String filepath;


    @Autowired
    NamedParameterJdbcTemplate jdbcTemplate;


    public void redaFile() throws Exception {
        File fileFold = new File(filepath);
        readFile(fileFold);

    }


    public void readFile(File fold) throws IOException {
        File[] files = fold.listFiles();
        for (int i = 0; i < files.length; i++) {
            File file = files[i];
            if (file.isDirectory()) {
                readFile(file);
            } else {
                if (file.getName().toLowerCase().endsWith(".xls") || file.getName().endsWith(".xlsx")) {
                    JSONArray jsonArray = ExcelUtils.reasExcelNoRepeat(file.getAbsolutePath(), 0);
                   boolean result = dowithData(jsonArray, file.getName());
                    if (result) {
                        File destFile = new File(filepath.replace("todo", "success"));
                        if (!destFile.isDirectory()) {
                            destFile.mkdirs();
                        }
                        FileUtils.copyFileToDirectory(file, destFile);
                        file.delete();
                    } else {
                        File destFile = new File(filepath.replace("todo", "failure"));
                        if (!destFile.isDirectory()) {
                            destFile.mkdirs();
                        }
                        FileUtils.copyFileToDirectory(file, destFile);
                        file.delete();
                    }
                } else {
                    File destFile = new File(filepath.replace("todo", "failure"));
                    FileUtils.copyFileToDirectory(file, destFile);
                    file.delete();
                }
            }
        }
    }

    public boolean dowithData(JSONArray jsonArray, String filename) {
        boolean isSuccess = true;
        try {
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);

                StringBuffer sb = new StringBuffer();
                Map map = new HashMap();
                StringBuffer str1 = new StringBuffer();
                str1.append("insert into  record(filename,no,createtime");
                StringBuffer str2 = new StringBuffer();
                str2.append("  values(:filename,:no,:createtime");
                for (String key : jsonObject.keySet()) {
                    String value = jsonObject.getString(key);
                    key = key.replace("(", "").replace(")", "").replace("%", "")
                            .replace("-", "").replace("●", "_").replace("★", "_")
                            .replace("（", "").replace("）", "").replace(" ", "")
                            .replace("＇", "").replace("/", "").trim().toLowerCase();
                    if (key.matches("^\\d.*")) {
                        key = '_' + key;
                    }


                    map.put("filename", filename);
                    map.put("no", i);
                    map.put("createtime", new java.util.Date());
                     if (StringUtils.isNotEmpty(value)) {
                        String sql = "alter  table  record   add " + key + "  varchar(100)  default ''";
                        this.jdbcTemplate.getJdbcOperations().update(sql);
                        map.put(key, value);
                        str1.append(",").append(key);
                        str2.append(",").append(":").append(key);
                    }
                }
                str1.append(")");
                str2.append(")");
                this.jdbcTemplate.update(str1.toString() + str2.toString(), map);
            }
        } catch (Exception err) {
            err.printStackTrace();
            isSuccess = false;
        }
        return isSuccess;
    }


    //   @Scheduled(cron = "0 45 1,11 ? * * ")
    public void outputFile() throws Exception {
        //  List<Record>  list=this.recordDao.findAll();
        //   XmlEntityVo xmlEntityVo=new XmlEntityVo(list);
        DataSource dataSource =((JdbcTemplate) (jdbcTemplate.getJdbcOperations())).getDataSource();
        Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("select *  from record");
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        String[] header = new String[resultSetMetaData.getColumnCount()];
        for (int i = 1; i < resultSetMetaData.getColumnCount() + 1; i++) {
            header[i - 1] = resultSetMetaData.getColumnName(i);
        }
        CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader(header);
        //    List<Map<String,Object>>  list=this.jdbcTemplate.getJdbcOperations().queryForList("select *  from record");
        String path = filepath.replace("todo", "export");
        File destFile = new File(path);
        if (!destFile.isDirectory()) {
            destFile.mkdirs();
        }
        String datatime = DateUtils.formatDate(new Date(), DateUtils.yyyyMMddHHmmss);
        String fullname = path + File.separator + datatime + ".csv";
        try (OutputStreamWriter outputStreamWriter = new OutputStreamWriter(new FileOutputStream(fullname),
                Charset.forName("GBK"));
             CSVPrinter csvPrinter = new CSVPrinter(outputStreamWriter, csvFormat)) {
            ;
            while (resultSet.next()) {
                List row = new ArrayList();
                for (int i = 1; i < resultSetMetaData.getColumnCount() + 1; i++) {
                    System.out.println(resultSet.getString(i));
                    if (resultSetMetaData.getColumnClassName(i).contains("Date")) {
                        row.add(resultSet.getDate(i));
                    } else if (resultSetMetaData.getColumnClassName(i).contains("Timestamp")) {
                        row.add(resultSet.getTimestamp(i));
                    } else if (resultSetMetaData.getColumnClassName(i).contains("Time")) {
                        row.add(resultSet.getTime(i));
                    } else if (resultSetMetaData.getColumnClassName(i).contains("Integer")) {
                        row.add(resultSet.getInt(i));
                    } else if (resultSetMetaData.getColumnClassName(i).contains("Float")) {
                        row.add(resultSet.getDouble(i));
                    } else if (resultSetMetaData.getColumnClassName(i).contains("Decimal")) {
                        row.add(resultSet.getBigDecimal(i));
                    } else if (resultSetMetaData.getColumnClassName(i).contains("String")) {
                        String value = resultSet.getString(i);
                        if (value == null) {
                            value = "";
                        }
                        if (value.matches("\\d+")&& (value.length()>10)) {
                            value = "'" + value;
                        }
                        row.add(value);
                    } else {
                        System.out.printf(resultSetMetaData.getColumnClassName(i));
                        row.add(resultSet.getString(i));
                    }
                }
                csvPrinter.printRecord(row);
            }

            csvPrinter.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }


//        List<Map<String, Object>> list = this.recordDao.getData();
//       //    List<Map<String,Object>>  list=this.jdbcTemplate.getJdbcOperations().queryForList("select *  from record");
//        String  path=filepath.replace("todo","export");
//        File destFile = new File(path);
//        if(!destFile.isDirectory()){
//            destFile.mkdirs();
//        }
//        String  datatime = DateUtils.formatDate(new Date(), DateUtils.yyyyMMddHHmmss);
//        String fullname = path +  File.separator+datatime+".csv" ;
//        Bean2XmlUtils.createCSV(fullname, list);


    }


}
